﻿using System;
using System.Data;
using System.Data.SqlServerCe;

namespace SqlCeDataAccess
{
  public static class Connect
  {
    private const int COMMAND_TIMEOUT = 0;

    /// <summary>
    /// Returns a filled dataset
    /// 
    /// </summary>
    /// <param name="procedure">Stored procedure to execute</param><param name="parameters">Parameters</param><param name="connection">Connection key</param>
    /// <returns/>
    private static DataSet FillDatSet(string query, Parameters parameters, string connection)
    {
      var selectCommand = new SqlCeCommand(query,
                                           new SqlCeConnection(SqlCeDataAccess.Environment.Connections[connection]))
        {
          CommandTimeout = COMMAND_TIMEOUT
        };

      if (parameters != null)
      {
        foreach (var sqlParameter in parameters)
          selectCommand.Parameters.Add(sqlParameter);
      }

      var sqlDataAdapter = new SqlCeDataAdapter(selectCommand);
      var dataSet = new DataSet();

      sqlDataAdapter.Fill(dataSet);
      selectCommand.Connection.Close();

      return dataSet;
    }

    /// <summary>
    /// Validates that given connections keys/names are valid
    /// 
    /// </summary>
    /// <param name="connection"/>
    private static void ValidateConnection(string connection)
    {
      if (connection == string.Empty)
        throw new UnknownConnection("Connection key/name cannot be empty.");
      if (SqlCeDataAccess.Environment.Connections[connection] == string.Empty)
        throw new UnknownConnection("Connection key/name '" + connection + "' refers to an unknown connection.");
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataSet.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    public static DataSet GetDataSet(string query)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.GetDataSet(query, (Parameters)null, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataSet.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param>
    public static DataSet GetDataSet(string query, Parameters parameters)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.GetDataSet(query, parameters, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataSet.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param><param name="connection">Connection key/name</param>
    public static DataSet GetDataSet(string query, Parameters parameters, string connection)
    {
      Connect.ValidateConnection(connection);
      return Connect.FillDatSet(query, parameters, connection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    public static DataTable GetDataTable(string query)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.GetDataTable(query, (Parameters)null, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param>
    public static DataTable GetDataTable(string query, Parameters parameters)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.GetDataTable(query, parameters, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param><param name="connection">Connection key/name</param>
    public static DataTable GetDataTable(string query, Parameters parameters, string connection)
    {
      return Connect.GetDataTable(query, parameters, connection, 0);
    }

    /// <summary>
    /// Execute the provided procedure and return the results in a DataTable.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param><param name="connection">Connection key/name</param><param name="tableIndex">Index within tables collection to return</param>
    public static DataTable GetDataTable(string query, Parameters parameters, string connection, int tableIndex)
    {
      Connect.ValidateConnection(connection);
      return Connect.FillDatSet(query, parameters, connection).Tables[tableIndex];
    }

    /// <summary>
    /// Executes the provided procedure and returns an open DataReader.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    public static SqlCeDataReader GetDataReader(string query)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.GetDataReader(query, (Parameters)null, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure and returns an open DataReader.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param>
    public static SqlCeDataReader GetDataReader(string query, Parameters parameters)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.GetDataReader(query, parameters, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure and returns an open DataReader.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param><param name="connection">Connection key/name</param>
    public static SqlCeDataReader GetDataReader(string query, Parameters parameters, string connection)
    {
      Connect.ValidateConnection(connection);

      var sqlCommand = new SqlCeCommand(query,
                                        new SqlCeConnection(SqlCeDataAccess.Environment.Connections[connection]))
        {
          CommandTimeout = COMMAND_TIMEOUT
        };

      if (parameters != null)
      {
        foreach (var sqlParameter in parameters)
          sqlCommand.Parameters.Add(sqlParameter);
      }

      if (sqlCommand.Connection.State != ConnectionState.Open)
        sqlCommand.Connection.Open();

      return sqlCommand.ExecuteReader();
    }

    /// <summary>
    /// Executes the provided procedure.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <returns>
    /// Stored procedure RETURN value.
    /// </returns>
    public static int Execute(string query)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.Execute(query, (Parameters)null, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param>
    /// <returns>
    /// Stored procedure RETURN value.
    /// </returns>
    public static int Execute(string query, Parameters parameters)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.Execute(query, parameters, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param><param name="connection">Connection key/name</param>
    /// <returns>
    /// Stored procedure RETURN value.
    /// </returns>
    public static int Execute(string query, Parameters parameters, string connection)
    {
      Connect.ValidateConnection(connection);

      var sqlCommand = new SqlCeCommand(query,
                                        new SqlCeConnection(SqlCeDataAccess.Environment.Connections[connection]))
        {
          CommandTimeout = COMMAND_TIMEOUT
        };

      if (parameters != null)
      {
        foreach (var sqlParameter in parameters)
          sqlCommand.Parameters.Add(sqlParameter);
      }

      //var sqlParameter1 = new SqlCeParameter("@RETURN_VALUE", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue };

      //sqlCommand.Parameters.Add(sqlParameter1);

      if (sqlCommand.Connection.State != ConnectionState.Open)
        sqlCommand.Connection.Open();

      sqlCommand.ExecuteNonQuery();
      sqlCommand.Connection.Close();

      return Convert.ToInt32(0);//sqlParameter1.Value);
    }

    /// <summary>
    /// Executes the provided procedure and returns a single value.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param>
    /// <returns>
    /// Object containing the result
    /// </returns>
    public static object ExecuteScalar(string query)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.ExecuteScalar(query, (Parameters)null, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure and returns a single value.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param>
    /// <returns>
    /// Object containing the result
    /// </returns>
    public static object ExecuteScalar(string query, Parameters parameters)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.ExecuteScalar(query, parameters, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    /// <summary>
    /// Executes the provided procedure and returns a single value.
    /// 
    /// </summary>
    /// <param name="procedure">Stored Procedure to execute</param><param name="parameters">Parameter list</param><param name="connection">Connection key/name</param>
    /// <returns>
    /// Object containing the result
    /// </returns>
    public static object ExecuteScalar(string query, Parameters parameters, string connection)
    {
      Connect.ValidateConnection(connection);

      var sqlCommand = new SqlCeCommand(query,
                                        new SqlCeConnection(SqlCeDataAccess.Environment.Connections[connection]))
        {
          CommandTimeout = COMMAND_TIMEOUT
        };

      if (parameters != null)
      {
        foreach (var sqlParameter in parameters)
          sqlCommand.Parameters.Add(sqlParameter);
      }

      if (sqlCommand.Connection.State != ConnectionState.Open)
        sqlCommand.Connection.Open();

      var obj = sqlCommand.ExecuteScalar();

      sqlCommand.Connection.Close();

      return obj;
    }

    public static object ExecuteInsertWithOutput(string query, Parameters parameters)
    {
      if (SqlCeDataAccess.Environment.Connections.DefaultConnection == string.Empty)
        throw new DefaultConnectionNotDefined("The Default Connection has not been defined. Please define prior to use.");
      else
        return Connect.ExecuteInsertWithOutput(query, parameters, SqlCeDataAccess.Environment.Connections.DefaultConnection);
    }

    public static object ExecuteInsertWithOutput(string query, Parameters parameters, string connection)
    {
      Connect.ValidateConnection(connection);

      var sqlCommand = new SqlCeCommand(query,
                                        new SqlCeConnection(SqlCeDataAccess.Environment.Connections[connection]))
        {
          CommandTimeout = COMMAND_TIMEOUT
        };

      if (parameters != null)
      {
        foreach (var sqlParameter in parameters)
          sqlCommand.Parameters.Add(sqlParameter);
      }

      if (sqlCommand.Connection.State != ConnectionState.Open)
        sqlCommand.Connection.Open();

      sqlCommand.ExecuteNonQuery();

      sqlCommand.CommandText = "SELECT @@IDENTITY";

      var obj = sqlCommand.ExecuteScalar();

      sqlCommand.Connection.Close();

      return obj;
    }
  }
}
